const ChineseMedicine = require("../model/ChineseMedicine");
const { Sequelize } = require("sequelize");
module.exports = class ChineseMedicineManager {
  /**
   * 创建新的中药记录
   * @param {Object} medicineData - 中药记录数据
   * @returns {Promise<Model>} 新创建的记录
   */
  static async create(medicineData) {
    try {
      const result = await ChineseMedicine.create(medicineData);
      return result.toJSON();
    } catch (error) {
      throw new Error(`创建中药记录失败: ${error.message}`);
    }
  }
  /**
   * 根据sn获取中药记录
   * @param {number} sn - 记录sn
   * @returns {Promise<Model|null>} 找到的记录或null
   */
  static async getBySn(sn) {
    try {
      return await ChineseMedicine.findOne({
        where: {
          sn: sn,
        },
      });
    } catch (error) {
      throw new Error(`获取中药记录失败: ${error.message}`);
    }
  }

  /**
   * 更新
   * @param {*} sn
   * @param {*} Obj
   */
  static async update(sn, Obj) {
    await ChineseMedicine.update(Obj, {
      where: {
        sn: sn,
      },
    });
    const result = await ChineseMedicine.findOne({
      where: {
        sn: sn,
      },
    });
    return result;
  }

  /**
   * 删除
   * @param {*} sn
   */
  static async delete(sn) {
    const result = await ChineseMedicine.destroy({
      where: {
        sn: sn,
      },
    });
  }

  /**
   * 分页查询
   * @returns
   */
  static async getByPage(page, limit, where) {
    const params = {
      offset: (page - 1) * limit,
      limit: +limit,
      order: [["createdAt", "DESC"]],
    };
    if (where) {
      params.where = where;
    }
    const result = await ChineseMedicine.findAndCountAll({
      ...params,
    });
    if (result === null) {
      console.log("Not found!");
    } else {
      return JSON.parse(JSON.stringify(result));
    }
  }
  /**
   * 批量新增中药记录
   * @param {Array} medicineDataList - 中药记录数据数组
   * @param {Object} [options] - 可选参数
   * @param {boolean} [options.validate=true] - 是否验证每条数据
   * @param {boolean} [options.ignoreDuplicates=false] - 是否忽略重复记录
   * @returns {Promise<Array>} 成功创建的记录数组
   */
  static async bulkCreate(medicineDataList, options = {}) {
    const defaultOptions = {
      validate: true,
      ignoreDuplicates: false,
      ...options,
    };

    if (!Array.isArray(medicineDataList) || medicineDataList.length === 0) {
      throw new Error("请输入有效的药品数据数组");
    }

    try {
      // 批量创建记录
      const results = await ChineseMedicine.bulkCreate(medicineDataList, {
        validate: defaultOptions.validate,
        ignoreDuplicates: defaultOptions.ignoreDuplicates,
        returning: true, // 返回创建的记录
      });

      // 转换为普通对象
      return results.map((record) => record.toJSON());
    } catch (error) {
      throw new Error(`批量创建中药记录失败: ${error.message}`);
    }
  }
  //根据科室统计
  static async countByDepartment(options = {}) {
    try {
      const result = await ChineseMedicine.findAll({
        attributes: [
          "department",
          [Sequelize.fn("COUNT", Sequelize.col("id")), "count"],
        ],
        group: "department",
        where: options.where || {},
        raw: true,
        order: [[Sequelize.literal("count"), "DESC"]], // 按数量降序排列
      });

      // 处理结果，确保无科室的记录也被统计
      const formattedResult = result.map((item) => ({
        name: item.department || "未分类",
        value: parseInt(item.count, 10),
      }));

      return formattedResult;
    } catch (error) {
      throw new Error(`科室统计失败: ${error.message}`);
    }
  }
  //根据性别统计
  static async countBySex(options = {}) {
    try {
      const result = await ChineseMedicine.findAll({
        attributes: [
          "sex",
          [Sequelize.fn("COUNT", Sequelize.col("id")), "count"],
        ],
        group: "sex",
        where: options.where || {},
        raw: true,
        order: [[Sequelize.literal("count"), "DESC"]], // 按数量降序排列
      });
      // 标准化性别字段（处理null/undefined和中文值）
      const formattedResult = result.map((item) => ({
        name: item.sex,
        value: parseInt(item.count, 10),
      }));
      return formattedResult;
    } catch (error) {
      throw new Error(`性别统计失败: ${error.message}`);
    }
  }
  /**
   * 分性别统计年龄数量（可按年龄段分组）
   * @param {Object} [options] - 可选参数
   * @param {Object} [options.where] - 筛选条件
   * @param {number} [options.ageInterval=5] - 年龄分组间隔（默认5岁一组）
   * @returns {Promise<Array>} 返回统计数组，格式:
   * [
   *   { sex: '男', ageGroup: '20-24', count: 15 },
   *   { sex: '女', ageGroup: '25-29', count: 8 },
   *   ...
   * ]
   */
  static async countByAge(options = {}) {
    const ageInterval = options.ageInterval || 5;
    const whereClause = options.where || {};
    try {
      // 计算年龄分组表达式
      const ageGroupExpr = Sequelize.literal(
        `CONCAT(
        FLOOR(age/${ageInterval})*${ageInterval}, 
        '-', 
        FLOOR(age/${ageInterval})*${ageInterval} + ${ageInterval - 1}
      )`
      );

      const result = await ChineseMedicine.findAll({
        attributes: [
          "sex",
          [ageGroupExpr, "ageGroup"],
          [Sequelize.fn("COUNT", Sequelize.col("id")), "count"],
        ],
        where: whereClause,
        group: ["sex", "ageGroup"],
        raw: true,
        order: [
          ["sex", "ASC"],
          [Sequelize.literal("ageGroup"), "ASC"],
        ],
      });

      // 标准化结果
      return result.map((item) => ({
        sex: item.sex || "未知",
        ageGroup: item.ageGroup,
        count: parseInt(item.count, 10),
      }));
    } catch (error) {
      throw new Error(`年龄性别统计失败: ${error.message}`);
    }
  }
  //根据部位统计
  static async countByPos(options = {}) {
    try {
      const result = await ChineseMedicine.findAll({
        attributes: [
          "pos",
          [Sequelize.fn("COUNT", Sequelize.col("id")), "count"],
        ],
        group: "pos",
        where: options.where || {},
        raw: true,
        order: [[Sequelize.literal("count"), "DESC"]], // 按数量降序排列
      });

      // 处理结果，确保无科室的记录也被统计
      const formattedResult = result.map((item) => ({
        name: item.pos || "未分类",
        value: parseInt(item.count, 10),
      })).sort((a,b)=>a-b);

      return formattedResult;
    } catch (error) {
      throw new Error(`部位统计失败: ${error.message}`);
    }
  }
};
